﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Xml;
using Agresso.Interface.Layer.Domain;
using System.Xml.Linq;
using Agresso.Interface.Layer.Gateway;
using System.Data.SqlClient;
using Agresso.Interface.Layer.SQL;
using System.Data;
using System.Globalization;
using System.IO;
using System.Configuration;

namespace Agresso.Interface.Layer.Service
{
    public class DADesk_PDA_Service
    {
        DataBaseConnectivity m_dataBaseConectivity;
        ConfigurationSettingService m_configSettingService;
        AcrBatcInputService m_AcrBatcInputService;
        public DADesk_PDA_Service()
        {
            m_dataBaseConectivity = new DataBaseConnectivity();
            m_configSettingService = new ConfigurationSettingService();
            m_AcrBatcInputService = new AcrBatcInputService();
        }
        public IList GetFromXML(string file)
        {
            IList result = new ArrayList();
            try
            {
                XmlDocument doc = new XmlDocument();
                doc.Load(file);
                XmlNodeList hub_interface_uploads = doc.SelectNodes("hub-interface-upload");
                foreach (XmlNode n in hub_interface_uploads)
                {
                    XmlNodeList file_imports = n.SelectNodes("file-import");
                    foreach (XmlNode pdaXML in file_imports)
                    {
                        DADesk_PDA pda = new DADesk_PDA();
                        pda.FileName = Path.GetFileName(file);
                        //revision
                        //pda.AccountCode = pdaXML.SelectNodes("AccountCode")[0].LastChild.Value;
                        //pda.AgentCode = pdaXML.SelectNodes("AgentCode")[0].LastChild.Value;
                        //pda.AgentName = pdaXML.SelectNodes("AgentName")[0].LastChild.Value;
                        //pda.Amount = Convert.ToDecimal(pdaXML.SelectNodes("Amount")[0].LastChild.Value);
                        //pda.Company = pdaXML.SelectNodes("Company")[0].LastChild.Value;
                        //pda.Currency = pdaXML.SelectNodes("Currency")[0].LastChild.Value;
                        //pda.DADeskRef = pdaXML.SelectNodes("DADeskRef")[0].LastChild.Value;
                        //pda.DAType = pdaXML.SelectNodes("DAType")[0].LastChild.Value;
                        //pda.ETA = Convert.ToDateTime(pdaXML.SelectNodes("ETA")[0].LastChild.Value);
                        //pda.PortCode = pdaXML.SelectNodes("PortCode")[0].LastChild.Value;
                        //pda.TransactionDate = Convert.ToDateTime(pdaXML.SelectNodes("TransactionDate")[0].LastChild.Value);
                        //pda.VesselCode = pdaXML.SelectNodes("VesselCode")[0].LastChild.Value;
                        //pda.VesselName = pdaXML.SelectNodes("VesselName")[0].LastChild.Value;
                        //pda.VoyageNo = pdaXML.SelectNodes("VoyageNo")[0].LastChild.Value;
                        pda.AccountCode = pdaXML.SelectNodes("AccountCode")[0].LastChild == null ? "" : pdaXML.SelectNodes("AccountCode")[0].LastChild.Value;
                        pda.AgentCode = pdaXML.SelectNodes("AgentCode")[0].LastChild == null ? "" : pdaXML.SelectNodes("AgentCode")[0].LastChild.Value;
                        pda.AgentName = pdaXML.SelectNodes("AgentName")[0].LastChild == null ? "" : pdaXML.SelectNodes("AgentName")[0].LastChild.Value;
                        pda.Amount = Convert.ToDecimal(pdaXML.SelectNodes("Amount")[0].LastChild == null ? "0" : pdaXML.SelectNodes("Amount")[0].LastChild.Value);
                        pda.Company = pdaXML.SelectNodes("Company")[0].LastChild == null ? "" : pdaXML.SelectNodes("Company")[0].LastChild.Value;
                        pda.Currency = pdaXML.SelectNodes("Currency")[0].LastChild == null ? "" : pdaXML.SelectNodes("Currency")[0].LastChild.Value;
                        pda.DADeskRef = pdaXML.SelectNodes("DADeskRef")[0].LastChild == null ? "" : pdaXML.SelectNodes("DADeskRef")[0].LastChild.Value;
                        pda.DAType = pdaXML.SelectNodes("DAType")[0].LastChild == null ? "" : pdaXML.SelectNodes("DAType")[0].LastChild.Value;
                       // pda.ETA = pdaXML.SelectNodes("ETA")[0].LastChild == null ? new DateTime(1900, 1, 1) : ConvertToDateTime(pdaXML.SelectNodes("ETA")[0].LastChild.Value);
                        pda.ETA = pdaXML.SelectNodes("ETA")[0].LastChild == null ? new DateTime(1900, 1, 1) : ConvertToDateTime(pdaXML.SelectNodes("ETA")[0].LastChild.Value);
                        pda.PortCode = pdaXML.SelectNodes("PortCode")[0].LastChild == null ? "" : pdaXML.SelectNodes("PortCode")[0].LastChild.Value;
                        pda.TransactionDate = pdaXML.SelectNodes("TransactionDate")[0].LastChild == null ? new DateTime(1900, 1, 1) : ConvertToDateTime(pdaXML.SelectNodes("TransactionDate")[0].LastChild.Value);
                        pda.VesselCode = pdaXML.SelectNodes("VesselCode")[0].LastChild == null ? "" : pdaXML.SelectNodes("VesselCode")[0].LastChild.Value;
                        pda.VesselName = pdaXML.SelectNodes("VesselName")[0].LastChild == null ? "" : pdaXML.SelectNodes("VesselName")[0].LastChild.Value;
                        pda.VoyageNo = pdaXML.SelectNodes("VoyageNo")[0].LastChild == null ? "" : pdaXML.SelectNodes("VoyageNo")[0].LastChild.Value;
                        result.Add(pda);
                    }
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            return result;
        }
        public DateTime ConvertToDateTime(string date)
        {
            if (date.Contains("T"))
                return ConvertToDateTimeWithT(date);
            else if (date.Contains("/"))
                return ConvertToDateTimeWithSlash(date);
            else
                return Convert.ToDateTime(date);
        }
        public DateTime ConvertToDateTimeWithT(string xmlDate)
        {
            //2014-01-2T4:55:13+04:00

            string[] Dt = xmlDate.Split(new[] { "T" }, StringSplitOptions.None);
            DateTime result = Convert.ToDateTime(Dt[0]);

            string timespan = Dt[1].Substring(Dt[1].Length - 5, 5);
            TimeSpan tm = new TimeSpan(Convert.ToInt32(timespan.Substring(0, 2)), Convert.ToInt32(timespan.Substring(3, 2)), 0);

            string time = Dt[1].Substring(0, Dt[1].Length - 6);
            string[] timehourminute = time.Split(':');
            int hour = Convert.ToInt32(timehourminute[0]);
            int minute = Convert.ToInt32(timehourminute[1]);
            int second = Convert.ToInt32(timehourminute[2]);

            string plus = Dt[1].Substring(Dt[1].Length - 6, 1);

            result = new DateTime(result.Year, result.Month, result.Day, hour, minute, second);
            if (plus == "+")
                result = result.Add(tm);
            else
                result = result.Subtract(tm);
            return result;
        }
        private DateTime ConvertToDateTimeWithSlash(string p)
        {
            string[] date = p.Split('/');
            string[] yearTime = date[2].Split(' ');
            string[] time = yearTime[1].Split(':');
            return new DateTime(Convert.ToInt32(yearTime[0]), Convert.ToInt32(date[1]), Convert.ToInt32(date[0]), Convert.ToInt32(time[0]), Convert.ToInt32(time[1]), Convert.ToInt32(time[2]));
        }
        public bool AssertAssistPDA(DADesk_PDA pda)
        {
            bool result = false;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand(DA_Desk_SQL.AssertExistPDA(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("@AccountCode", System.Data.SqlDbType.NVarChar).Value = pda.AccountCode;
                cmd.Parameters.Add("@AgentCode", System.Data.SqlDbType.NVarChar).Value = pda.AgentCode;
                cmd.Parameters.Add("@AgentName", System.Data.SqlDbType.NVarChar).Value = pda.AgentName;
                cmd.Parameters.Add("@Amount", System.Data.SqlDbType.Float).Value = pda.Amount;
                cmd.Parameters.Add("@Company", System.Data.SqlDbType.NVarChar).Value = pda.Company;
                cmd.Parameters.Add("@Currency", System.Data.SqlDbType.NVarChar).Value = pda.Currency;
                cmd.Parameters.Add("@DADeskRef", System.Data.SqlDbType.NVarChar).Value = pda.DADeskRef;
                cmd.Parameters.Add("@DAType", System.Data.SqlDbType.NVarChar).Value = pda.DAType;
                cmd.Parameters.Add("@ETA", System.Data.SqlDbType.DateTime).Value = pda.ETA;
                cmd.Parameters.Add("@PortCode", System.Data.SqlDbType.NVarChar).Value = pda.PortCode;
                cmd.Parameters.Add("@TransactionDate", System.Data.SqlDbType.DateTime).Value = pda.TransactionDate;
                cmd.Parameters.Add("@VesselCode", System.Data.SqlDbType.NVarChar).Value = pda.VesselCode;
                cmd.Parameters.Add("@VesselName", System.Data.SqlDbType.NVarChar).Value = pda.VesselName;
                cmd.Parameters.Add("@VoyageNo", System.Data.SqlDbType.NVarChar).Value = pda.VoyageNo;
                LogService.WriteLogExecuteSQLCommand(cmd);
                int r = Convert.ToInt16(cmd.ExecuteScalar());
                result = r > 0;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }
        public void InsertPDA(DADesk_PDA pda)
        {
            try
            {
                if (AssertAssistPDA(pda)) return;
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand(DA_Desk_SQL.InsertPDA(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("@AccountCode", System.Data.SqlDbType.NVarChar).Value = pda.AccountCode;
                cmd.Parameters.Add("@AgentCode", System.Data.SqlDbType.NVarChar).Value = pda.AgentCode;
                cmd.Parameters.Add("@AgentName", System.Data.SqlDbType.NVarChar).Value = pda.AgentName;
                cmd.Parameters.Add("@Amount", System.Data.SqlDbType.Float).Value = pda.Amount;
                cmd.Parameters.Add("@Company", System.Data.SqlDbType.NVarChar).Value = pda.Company;
                cmd.Parameters.Add("@Currency", System.Data.SqlDbType.NVarChar).Value = pda.Currency;
                cmd.Parameters.Add("@DADeskRef", System.Data.SqlDbType.NVarChar).Value = pda.DADeskRef;
                cmd.Parameters.Add("@DAType", System.Data.SqlDbType.NVarChar).Value = pda.DAType;
                cmd.Parameters.Add("@ETA", System.Data.SqlDbType.DateTime).Value = pda.ETA;
                cmd.Parameters.Add("@PortCode", System.Data.SqlDbType.NVarChar).Value = pda.PortCode;
                cmd.Parameters.Add("@TransactionDate", System.Data.SqlDbType.DateTime).Value = pda.TransactionDate;
                cmd.Parameters.Add("@VesselCode", System.Data.SqlDbType.NVarChar).Value = pda.VesselCode;
                cmd.Parameters.Add("@VesselName", System.Data.SqlDbType.NVarChar).Value = pda.VesselName;
                cmd.Parameters.Add("@VoyageNo", System.Data.SqlDbType.NVarChar).Value = pda.VoyageNo;
                cmd.Parameters.Add("@CreatedDate", System.Data.SqlDbType.DateTime).Value = pda.CreatedDate;
                cmd.Parameters.Add("@ModifiedDate", System.Data.SqlDbType.DateTime).Value = pda.ModifiedDate;
                cmd.Parameters.Add("@Status", System.Data.SqlDbType.NVarChar).Value = pda.Status;
                cmd.Parameters.Add("@Remark", System.Data.SqlDbType.NVarChar).Value = pda.Remark;
                cmd.Parameters.Add("@FileName", System.Data.SqlDbType.NVarChar).Value = pda.FileName;
                LogService.WriteLogExecuteSQLCommand(cmd);
                cmd.ExecuteNonQuery();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public IList GetAllVesselMapping(string p)
        {
            IList result = new ArrayList();
            return result;
        }
        public IList GetAllVesselMapping()
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand(DA_Desk_SQL.GetAllVessel(), m_dataBaseConectivity.InterfaceSqlConnection);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                    result.Add(new DaDeskVessel(rd));
                rd.Close();
            }
            catch (Exception x)
            { 
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }
        public IList GetAllAgentMapping()
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand(DA_Desk_SQL.GetAllAgent(), m_dataBaseConectivity.InterfaceSqlConnection);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                    result.Add(new DaDeskAgent(rd));
                rd.Close();
            }
            catch (Exception x)
            {
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }
        public IList GetAllBankMapping()
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand(DA_Desk_SQL.GetAllBank(), m_dataBaseConectivity.InterfaceSqlConnection);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                    result.Add(new DaDeskBank(rd));
                rd.Close();
            }
            catch (Exception x)
            {
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }
        public IList GetAllCompanyMapping()
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand(DA_Desk_SQL.GetAllCompany(), m_dataBaseConectivity.InterfaceSqlConnection);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                    result.Add(new DaDeskCompany(rd));
                rd.Close();
            }
            catch (Exception x)
            {
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }
        public IList GetAllAccountMapping()
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand(DA_Desk_SQL.GetAllAccount(), m_dataBaseConectivity.InterfaceSqlConnection);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                    result.Add(new DaDeskAccount(rd));
                rd.Close();
            }
            catch (Exception x)
            {
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }
        public IList GetAllPortMapping()
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand(DA_Desk_SQL.GetAllPort(), m_dataBaseConectivity.InterfaceSqlConnection);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                    result.Add(new DaDeskPort(rd));
                rd.Close();
            }
            catch (Exception x)
            {
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }
        public void ExecuteInterfaceFDA()
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                IList pdaList = new ArrayList();
                SqlCommand cmd = new SqlCommand(DA_Desk_SQL.GetOutStandingDADeskRefFDA(), m_dataBaseConectivity.InterfaceSqlConnection);
                SqlDataReader rd = cmd.ExecuteReader();
                IList DADeskRef = new ArrayList();
                while (rd.Read())
                {
                    DADeskRef.Add(rd["DADeskRef"]);
                }
                rd.Close();
                cmd.CommandText = DA_Desk_SQL.GetOutStandingFDA();
                foreach (string refr in DADeskRef)
                {
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("@DADeskRef", SqlDbType.NVarChar).Value = refr;
                    rd = cmd.ExecuteReader();
                    DADesk_PDA header = null;
                    while (rd.Read())
                    {
                        if (header == null)
                        {
                            header = new DADesk_PDA(rd);
                            header.Items.Add(header);
                        }
                        else
                            header.Items.Add(new DADesk_PDA(rd));
                    }
                    rd.Close();
                    if (header != null)
                        pdaList.Add(header);
                }
                foreach (DADesk_PDA pda in pdaList)
                {
                    DaDeskCompany company = FindCompanyByName(pda.Company);
                    DaDeskAgent agent = FindAgentByName(pda.AgentName);
                    DaDeskVessel vessel = FindVesselByName(pda.VesselName);
                    //DaDeskAccount account = FindAccountByName(pda.AccountCode);
                    DaDeskPort port = FindPortByName(pda.PortCode);
                    DaDeskAccount accountAPARID = FindAccountByName("apar_id_fda");
                    string message = "";

                    if (company.AgrClient == "") message += "-Please Map the Company Client \r\n";
                    if (company.AgrCompany_Code == "") message += "-Please Map the Company Code \r\n";
                    if (agent.AgrAgent_Code == "") message += "-Please Map the Agent Code \r\n";
                    if (vessel.AgrVessel_CostCentre == "") message += "-Please Map the Vessel Cost Centre Code \r\n";
                    if (vessel.AgrVessel_Code == "") message += "-Please Map the Vessel Code \r\n";
                   // if (account.AgrAccountCode == "") message += "-Please Map the Account Code \r\n";
                    if (accountAPARID.AgrAccountCode == "") message += "-Please Map the APAR_ID Account Code \r\n";
                    if (port.AgrPortCode == "") message += "-Please Map the PORT Code \r\n";

                    foreach (DADesk_PDA item in pda.Items)
                    {
                        DaDeskAccount account = FindAccountByName(item.AccountCode);
                        if (account.AgrAccountCode == "") message += "-Please Map the Account Code " + item.AccountCode + " \r\n";
                    }

                    if (message.Trim() != string.Empty)
                    {
                      //  UpdateRemarksDetail(message, 0, "", pda);
                        continue;
                    }

                    StringBuilder vyg = new StringBuilder();
                    int voyage = 0;
                    for (int i = 0; i < pda.VoyageNo.Length; i++)
                    {
                        try
                        {
                            int v = Convert.ToInt32(pda.VoyageNo.Substring(i, 1));
                            vyg.Append(v);
                            voyage = Convert.ToInt32(vyg.ToString());
                        }
                        catch { }
                    }

                    int voucherNumber = GetVoucherRunningNumber();
                    IList acrbatchinputList = new ArrayList();

                    string batch_id = "DC" + DateTime.Now.ToString("ddMMyyyyhhmmss");
                    acrbatchinput acrbatchinput_AP = new acrbatchinput();
                    acrbatchinput_AP.voucher_type = "DC";
                    acrbatchinput_AP.trans_type = "AP";//"GL"
                    acrbatchinput_AP.client = company.AgrClient;
                    acrbatchinput_AP.apar_type = "P"; // 20.03.2014
                    acrbatchinput_AP.apar_id = agent.AgrAgent_Code; //20.03.2014    
                    acrbatchinput_AP.apar_name = "";// agent.AgrAgent_Name;
                    acrbatchinput_AP.account = accountAPARID.AgrAccountCode;
                    acrbatchinput_AP.dim_1 = vessel.AgrVessel_CostCentre;
                    acrbatchinput_AP.dim_2 = "";
                    acrbatchinput_AP.dim_3 = port.AgrPortCode; //20.03.2014
                    acrbatchinput_AP.dim_4 = vessel.AgrVessel_Code + "-V" + voyage.ToString().PadLeft(3, '0');//25042014
                    acrbatchinput_AP.dim_5 = vessel.AgrVessel_Code;
                    acrbatchinput_AP.dim_6 = company.AgrCompany_Code;
                    acrbatchinput_AP.dim_7 = "";
                    acrbatchinput_AP.tax_code = "0";
                    acrbatchinput_AP.tax_system = "";
                    acrbatchinput_AP.currency = pda.Currency;
                    acrbatchinput_AP.dc_flag = 0;
                    acrbatchinput_AP.cur_amount = 0;// pda.Amount;
                    acrbatchinput_AP.amount = 0;
                    acrbatchinput_AP.value_1 = 0;
                    acrbatchinput_AP.value_2 = 0;
                    acrbatchinput_AP.value_3 = 0;
                    acrbatchinput_AP.description = pda.DADeskRef + "," + pda.PortCode + ", ETA:" + pda.ETA.ToString("yyyyMMdd") + "," + agent.AgrAgent_Code + "-" + agent.AgrAgent_Name;
                    acrbatchinput_AP.trans_date = pda.TransactionDate;
                    acrbatchinput_AP.voucher_date = pda.TransactionDate.Date;
                    acrbatchinput_AP.voucher_no = voucherNumber;
                    acrbatchinput_AP.ext_inv_ref = pda.DADeskRef;
                    acrbatchinput_AP.status = "N";
                    acrbatchinput_AP.due_date = pda.TransactionDate;
                    acrbatchinput_AP.period = Convert.ToInt32(DateTime.Today.ToString("yyyyMM"));
                    acrbatchinput_AP.responsible = "DEV01";
                    acrbatchinput_AP.batch_id = batch_id;
                    acrbatchinput_AP.interface_ = "DC";
                    acrbatchinput_AP.disc_date = pda.TransactionDate;
                    acrbatchinput_AP.sequence_no = GetSequenceRunningNumber();
                    acrbatchinputList.Add(acrbatchinput_AP);


                    foreach (DADesk_PDA item in pda.Items)
                    {
                        DaDeskAccount account = FindAccountByName(item.AccountCode);
                        acrbatchinput acrbatchinput_GL = new acrbatchinput();
                        acrbatchinput_AP.cur_amount += item.Amount;
                        acrbatchinput_GL.voucher_type = "DC";
                        acrbatchinput_GL.trans_type = "GL";
                        acrbatchinput_GL.client = company.AgrClient;
                        acrbatchinput_GL.apar_type = "";
                        acrbatchinput_GL.apar_id = "";// agent.AgrAgent_Code;
                        acrbatchinput_GL.apar_name = "";//agent.AgrAgent_Name;
                        acrbatchinput_GL.account = account.AgrAccountCode;
                        acrbatchinput_GL.dim_1 = vessel.AgrVessel_CostCentre;
                        acrbatchinput_GL.dim_2 = "";
                        acrbatchinput_GL.dim_3 = port.AgrPortCode; //20.03.2014
                        acrbatchinput_GL.dim_4 = vessel.AgrVessel_Code + "-V" + voyage.ToString().PadLeft(3, '0');
                        acrbatchinput_GL.dim_5 = vessel.AgrVessel_Code;
                        acrbatchinput_GL.dim_6 = company.AgrCompany_Code;
                        acrbatchinput_GL.dim_7 = "";
                        acrbatchinput_GL.tax_code = "0";
                        acrbatchinput_GL.tax_system = "";
                        acrbatchinput_GL.currency = pda.Currency;
                        acrbatchinput_GL.dc_flag = 0;
                        acrbatchinput_GL.cur_amount = -item.Amount;
                        acrbatchinput_GL.amount = 0;
                        acrbatchinput_GL.value_1 = 0;
                        acrbatchinput_GL.value_2 = 0;
                        acrbatchinput_GL.value_3 = 0;
                        acrbatchinput_GL.description = acrbatchinput_AP.description;
                        acrbatchinput_GL.trans_date = pda.TransactionDate;
                        acrbatchinput_GL.voucher_date = pda.TransactionDate.Date;
                        acrbatchinput_GL.voucher_no = voucherNumber;
                        acrbatchinput_GL.ext_inv_ref = pda.DADeskRef;
                        acrbatchinput_GL.status = "N";
                        acrbatchinput_GL.due_date = pda.TransactionDate;
                        acrbatchinput_GL.period = Convert.ToInt32(DateTime.Today.ToString("yyyyMM"));
                        acrbatchinput_GL.responsible = "DEV01";
                        acrbatchinput_GL.batch_id = batch_id;
                        acrbatchinput_GL.interface_ = "DC";
                        acrbatchinput_GL.disc_date = pda.TransactionDate;
                        acrbatchinput_GL.sequence_no = GetSequenceRunningNumber();
                        acrbatchinputList.Add(acrbatchinput_GL);
                    }
                    //insert ACRBATCHinput
                    string agrtid = "";
                    foreach (acrbatchinput acr in acrbatchinputList)
                    {
                        //m_AcrBatcInputService.Insert(acr);
                        m_AcrBatcInputService.InsertToInterfaceTable(acr);
                        agrtid += ";" + acr.agrtid.ToString();
                    }
                    //update status-------
                    if (agrtid != "")
                    {
                        //  UpdateRemarksDetail("", 1, ((acrbatchinput)acrbatchinputList[0]).batch_id, pda);
                    }
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public void ExecuteInterfacePDA()
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                IList pdaList = new ArrayList();
                SqlCommand cmd = new SqlCommand(DA_Desk_SQL.GetOutStandingDADeskRefPDA(), m_dataBaseConectivity.InterfaceSqlConnection);
                SqlDataReader rd = cmd.ExecuteReader();
                IList DADeskRef = new ArrayList();
                while (rd.Read())
                {
                    DADeskRef.Add(rd["DADeskRef"]);
                }
                rd.Close();
                cmd.CommandText = DA_Desk_SQL.GetOutStandingPDA();
                foreach (string refr in DADeskRef)
                {
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("@DADeskRef", SqlDbType.NVarChar).Value = refr;
                    rd = cmd.ExecuteReader();
                    DADesk_PDA header = null;
                    while (rd.Read())
                    {
                        if (header == null)
                        {
                            header = new DADesk_PDA(rd);
                            header.Items.Add(header);
                        }
                        else
                            header.Items.Add(new DADesk_PDA(rd));
                    }
                    rd.Close();
                    if (header != null)
                        pdaList.Add(header);
                }

                foreach (DADesk_PDA pda in pdaList)
                {
                    DaDeskCompany company = FindCompanyByName(pda.Company);
                    DaDeskAgent agent = FindAgentByName(pda.AgentName);
                    DaDeskVessel vessel = FindVesselByName(pda.VesselName);
                    //DaDeskAccount account = FindAccountByName(pda.AccountCode);
                    DaDeskPort port = FindPortByName(pda.PortCode);
                    DaDeskAccount accountAPARID = pda.Company.ToLower().Trim() == "pcl" ? FindAccountByName("apar_id_pda_pcl") :
                                                    pda.Company.ToLower().Trim() == "psm" ? FindAccountByName("apar_id_pda_psm") :
                                                    FindAccountByName("apar_id_pda_pcl");

                    string message = "";

                    if (company.AgrClient == "") message += "-Please Map the Company Client \r\n";
                    if (company.AgrCompany_Code == "") message += "-Please Map the Company Code \r\n";
                    if (agent.AgrAgent_Code == "") message += "-Please Map the Agent Code \r\n";
                    if (vessel.AgrVessel_CostCentre == "") message += "-Please Map the Vessel Cost Centre Code \r\n";
                    if (vessel.AgrVessel_Code == "") message += "-Please Map the Vessel Code \r\n";
                   // if (account.AgrAccountCode == "") message += "-Please Map the Account Code \r\n";
                    if (accountAPARID.AgrAccountCode == "") message += "-Please Map the APAR_ID Account Code \r\n";
                    if (port.AgrPortCode == "") message += "-Please Map the PORT Code \r\n";

                    foreach (DADesk_PDA item in pda.Items)
                    {
                        DaDeskAccount account = FindAccountByName(item.AccountCode);
                        if (account.AgrAccountCode == "") message += "-Please Map the Account Code " + item.AccountCode + " \r\n";
                    }

                    if (message.Trim() != string.Empty)
                    {
                       // UpdateRemarksDetail(message, 0, "", pda);
                        continue;
                    }

                    StringBuilder vyg = new StringBuilder();
                    int voyage = 0;
                    for (int i = 0; i < pda.VoyageNo.Length; i++)
                    {
                        try
                        {
                            int v = Convert.ToInt32(pda.VoyageNo.Substring(i, 1));
                            vyg.Append(v);
                            voyage = Convert.ToInt32(vyg.ToString());
                        }
                        catch { }
                    }

                    int voucherNumber = GetVoucherRunningNumber();
                    IList acrbatchinputList = new ArrayList();

                        string batch_id = "DB" + DateTime.Now.ToString("ddMMyyyyhhmmss");

                        acrbatchinput acrbatchinput_AP = new acrbatchinput();
                        acrbatchinput_AP.voucher_type = "DB";
                        acrbatchinput_AP.trans_type = "GL";//AP
                        acrbatchinput_AP.client = company.AgrClient;
                        acrbatchinput_AP.apar_type = "";// "P";
                        acrbatchinput_AP.apar_id = "";// agent.AgrAgent_Code;
                        acrbatchinput_AP.apar_name = "";// agent.AgrAgent_Name;
                        acrbatchinput_AP.account = accountAPARID.AgrAccountCode;
                        acrbatchinput_AP.dim_1 = vessel.AgrVessel_CostCentre;
                        acrbatchinput_AP.dim_2 = "";
                        acrbatchinput_AP.dim_3 = pda.PortCode;
                        acrbatchinput_AP.dim_4 = pda.Company.ToLower().Trim() == "psm" ? vessel.AgrVessel_Code + "-V" + voyage.ToString().PadLeft(3, '0') : "";
                        acrbatchinput_AP.dim_5 = vessel.AgrVessel_Code;
                        acrbatchinput_AP.dim_6 = company.AgrCompany_Code;
                        acrbatchinput_AP.dim_7 = "";
                        acrbatchinput_AP.tax_code = "0";
                        acrbatchinput_AP.tax_system = "";
                        acrbatchinput_AP.currency = pda.Currency;
                        acrbatchinput_AP.dc_flag = 0;
                        acrbatchinput_AP.cur_amount = 0;// pda.Amount;
                        acrbatchinput_AP.amount = 0;
                        acrbatchinput_AP.value_1 = 0;
                        acrbatchinput_AP.value_2 = 0;
                        acrbatchinput_AP.value_3 = 0;
                        acrbatchinput_AP.description = pda.DADeskRef + "," + pda.PortCode + ", ETA:" + pda.ETA.ToString("yyyyMMdd") + "," + agent.AgrAgent_Code + "-" + agent.AgrAgent_Name;
                        acrbatchinput_AP.trans_date = pda.TransactionDate;
                        acrbatchinput_AP.voucher_date = pda.TransactionDate.Date;
                        acrbatchinput_AP.voucher_no = voucherNumber;    
                        acrbatchinput_AP.ext_inv_ref = pda.DADeskRef;
                        acrbatchinput_AP.status = "N";
                        acrbatchinput_AP.due_date = pda.TransactionDate;
                        acrbatchinput_AP.period = Convert.ToInt32(DateTime.Today.ToString("yyyyMM"));
                        acrbatchinput_AP.responsible = "DEV01";
                        acrbatchinput_AP.batch_id = batch_id;
                        acrbatchinput_AP.interface_ = "DB";
                        acrbatchinput_AP.disc_date = pda.TransactionDate;
                        acrbatchinput_AP.sequence_no = GetSequenceRunningNumber();
                        acrbatchinputList.Add(acrbatchinput_AP);

                        foreach (DADesk_PDA item in pda.Items)
                        {
                            acrbatchinput acrbatchinput_GL = new acrbatchinput();
                            DaDeskAccount account = FindAccountByName(item.AccountCode);
                            acrbatchinput_AP.cur_amount += item.Amount;
                            acrbatchinput_GL.voucher_type = "DB";
                            acrbatchinput_GL.trans_type = "GL";
                            acrbatchinput_GL.client = company.AgrClient;
                            acrbatchinput_GL.apar_type = "";
                            acrbatchinput_GL.apar_id = "";// agent.AgrAgent_Code;
                            acrbatchinput_GL.apar_name = "";//agent.AgrAgent_Name;
                            acrbatchinput_GL.account = account.AgrAccountCode;
                            acrbatchinput_GL.dim_1 = vessel.AgrVessel_CostCentre;
                            acrbatchinput_GL.dim_2 = "";
                            acrbatchinput_GL.dim_3 = pda.PortCode;
                            acrbatchinput_GL.dim_4 = vessel.AgrVessel_Code + "-V" + voyage.ToString().PadLeft(3, '0');
                            acrbatchinput_GL.dim_5 = vessel.AgrVessel_Code;
                            acrbatchinput_GL.dim_6 = company.AgrCompany_Code;
                            acrbatchinput_GL.dim_7 = "";
                            acrbatchinput_GL.tax_code = "0";
                            acrbatchinput_GL.tax_system = "";
                            acrbatchinput_GL.currency = pda.Currency;
                            acrbatchinput_GL.dc_flag = 0;
                            acrbatchinput_GL.cur_amount = -item.Amount;
                            acrbatchinput_GL.amount = 0;
                            acrbatchinput_GL.value_1 = 0;
                            acrbatchinput_GL.value_2 = 0;
                            acrbatchinput_GL.value_3 = 0;
                            acrbatchinput_GL.description = acrbatchinput_AP.description;
                            acrbatchinput_GL.trans_date = pda.TransactionDate;
                            acrbatchinput_GL.voucher_date = pda.TransactionDate.Date;
                            acrbatchinput_GL.voucher_no = voucherNumber;
                            acrbatchinput_GL.ext_inv_ref = pda.DADeskRef;
                            acrbatchinput_GL.status = "N";
                            acrbatchinput_GL.due_date = pda.TransactionDate;
                            acrbatchinput_GL.period = Convert.ToInt32(DateTime.Today.ToString("yyyyMM"));
                            acrbatchinput_GL.responsible = "DEV01";
                            acrbatchinput_GL.batch_id = batch_id;
                            acrbatchinput_GL.interface_ = "DB";
                            acrbatchinput_GL.disc_date = pda.TransactionDate;
                            acrbatchinput_GL.sequence_no = GetSequenceRunningNumber();
                            acrbatchinputList.Add(acrbatchinput_GL);
                        }
                
                    //insert ACRBATCHinput
                    string agrtid = "";
                    foreach (acrbatchinput acr in acrbatchinputList)
                    {
                     //   m_AcrBatcInputService.Insert(acr);
                        m_AcrBatcInputService.InsertToInterfaceTable(acr);
                     //   agrtid += ";" + acr.agrtid.ToString();
                    }
                    //update status-------
                    if (agrtid != "")
                    {
                        //  UpdateRemarksDetail("", 1, ((acrbatchinput)acrbatchinputList[0]).batch_id, pda);
                    }
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        private void UpdateRemarksDetail(string remarks, int pickupflag, string batch_id, DADesk_PDA das)
        {
            m_dataBaseConectivity.InterfaceSqlConnection_b.Open();
            SqlCommand cmd = new SqlCommand(DA_Desk_SQL.UpdateDADeskRemark(), m_dataBaseConectivity.InterfaceSqlConnection_b);
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@Status", SqlDbType.NVarChar).Value = pickupflag;
            cmd.Parameters.Add("@Remark", SqlDbType.NVarChar).Value = remarks;
            cmd.Parameters.Add("@batch_id", SqlDbType.NVarChar).Value = batch_id;
            cmd.Parameters.Add("@pda_id", SqlDbType.Int).Value = das.pda_id;
            cmd.ExecuteNonQuery();
            m_dataBaseConectivity.InterfaceSqlConnection_b.Close();
        }
        private int GetVoucherRunningNumber()
        {
            ConfigurationSetting runningNumber = m_configSettingService.GetConfigurationSetting("RunningNumber");
            int number = Convert.ToInt32(runningNumber.val_1) + 1;
            runningNumber.val_1 = number.ToString();
            m_configSettingService.UpdateConfiguration(runningNumber);
            return number;
        }
        private int GetSequenceRunningNumber()
        {
            ConfigurationSetting runningNumber = m_configSettingService.GetConfigurationSetting("RunningNumber");
            int number = Convert.ToInt32(runningNumber.val_2) + 1;
            runningNumber.val_2 = number.ToString();
            m_configSettingService.UpdateConfiguration(runningNumber);
            return number;
        }
        private DaDeskVessel FindVesselByName(string p)
        {
            DaDeskVessel result = null;
            m_dataBaseConectivity.InterfaceSqlConnection_b.Open();
            SqlCommand cmd = new SqlCommand(DA_Desk_SQL.FindVesselByName(), m_dataBaseConectivity.InterfaceSqlConnection);
            cmd.Parameters.Add("@Vessel_Name", SqlDbType.NVarChar).Value = p;
            SqlDataReader rd = cmd.ExecuteReader();
            while (rd.Read()) result = new DaDeskVessel(rd);
            rd.Close();
            if (result == null)
            {
                result = new DaDeskVessel();
                result.Vessel_Name = p;
                cmd.CommandText = DA_Desk_SQL.InsertVessel();
                cmd.Parameters.Clear();
                cmd.Parameters.Add("@Vessel_Name", SqlDbType.NVarChar).Value = result.Vessel_Name;
                cmd.Parameters.Add("@Vessel_Code", SqlDbType.NVarChar).Value = result.Vessel_Code;
                cmd.Parameters.Add("@AgrVessel_Code", SqlDbType.NVarChar).Value =result.AgrVessel_Code;
                cmd.Parameters.Add("@AgrVessel_Name", SqlDbType.NVarChar).Value = result.AgrVessel_Name;
                cmd.Parameters.Add("@AgrVessel_CostCentre", SqlDbType.NVarChar).Value = result.AgrVessel_CostCentre;
                cmd.ExecuteNonQuery();
            }
            m_dataBaseConectivity.InterfaceSqlConnection_b.Close();
            return result;
        }
        private DaDeskAgent FindAgentByName(string p)
        {
            DaDeskAgent result = null;
            m_dataBaseConectivity.InterfaceSqlConnection_b.Open();
            SqlCommand cmd = new SqlCommand(DA_Desk_SQL.FindAgentByName(), m_dataBaseConectivity.InterfaceSqlConnection);
            cmd.Parameters.Add("@Agent_Name", SqlDbType.NVarChar).Value = p;
            SqlDataReader rd = cmd.ExecuteReader();
            while (rd.Read()) result = new DaDeskAgent(rd);
            rd.Close();
            if (result == null)
            {
                result = new DaDeskAgent();
                result.Agent_Name = p;
                cmd.CommandText = DA_Desk_SQL.InsertAgent();
                cmd.Parameters.Clear();
                cmd.Parameters.Add("@Agent_Name", SqlDbType.NVarChar).Value = result.Agent_Name;
                cmd.Parameters.Add("@Agent_Code", SqlDbType.NVarChar).Value = result.Agent_Code;
                cmd.Parameters.Add("@AgrAgent_Code", SqlDbType.NVarChar).Value = result.AgrAgent_Code;
                cmd.Parameters.Add("@AgrAgent_Name", SqlDbType.NVarChar).Value = result.AgrAgent_Name;
                cmd.Parameters.Add("@AgrClient", SqlDbType.NVarChar).Value = result.AgrClient;
                cmd.ExecuteNonQuery();
            }
            m_dataBaseConectivity.InterfaceSqlConnection_b.Close();
            return result;
        }
        private DaDeskCompany FindCompanyByName(string p)
        {
            DaDeskCompany result = null;
            m_dataBaseConectivity.InterfaceSqlConnection_b.Open();
            SqlCommand cmd = new SqlCommand(DA_Desk_SQL.FindCompanyByName(), m_dataBaseConectivity.InterfaceSqlConnection);
            cmd.Parameters.Add("@Company", SqlDbType.NVarChar).Value = p;
            SqlDataReader rd = cmd.ExecuteReader();
            while (rd.Read()) result = new DaDeskCompany(rd);
            rd.Close();
            if (result == null)
            {
                result = new DaDeskCompany();
                result.Company = p;
                cmd.CommandText = DA_Desk_SQL.InsertCompany();
                cmd.Parameters.Clear();
                cmd.Parameters.Add("@Company", SqlDbType.NVarChar).Value = result.Company;
                cmd.Parameters.Add("@AgrCompany_Code", SqlDbType.NVarChar).Value = result.AgrCompany_Code;// 02062014 temporary
                cmd.Parameters.Add("@AgrCompany_Name", SqlDbType.NVarChar).Value = result.AgrCompany_Name;
                cmd.Parameters.Add("@AgrClient", SqlDbType.NVarChar).Value = result.AgrClient;
                cmd.ExecuteNonQuery();
            }
            m_dataBaseConectivity.InterfaceSqlConnection_b.Close();
            return result;
        }
        private DaDeskAccount FindAccountByName(string p)
        {
            DaDeskAccount result = null;
            p = p.ToLower();
            m_dataBaseConectivity.InterfaceSqlConnection_b.Open();
            SqlCommand cmd = new SqlCommand(DA_Desk_SQL.FindAccountByName(), m_dataBaseConectivity.InterfaceSqlConnection);
            cmd.Parameters.Add("@AccountCode", SqlDbType.NVarChar).Value = p;
            SqlDataReader rd = cmd.ExecuteReader();
            while (rd.Read()) result = new DaDeskAccount(rd);
            rd.Close();
            if (result == null)
            {
                result = new DaDeskAccount();
                result.AccountCode = p;
                result.AgrAccountCode = 
                    p.Trim() == "apar_id_fda" ? "2500" : 
                    p.Trim() == "apar_id_pda_pcl" ? "3301" :
                    p.Trim() == "apar_id_pda_psm" ? "3302" : 
                    "";//create for apar account
                cmd.CommandText = DA_Desk_SQL.InsertAccount();
                cmd.Parameters.Clear();
                cmd.Parameters.Add("@AccountCode", SqlDbType.NVarChar).Value = result.AccountCode;
                cmd.Parameters.Add("@AgrAccountCode", SqlDbType.NVarChar).Value = result.AgrAccountCode;
                cmd.ExecuteNonQuery();
            }
            m_dataBaseConectivity.InterfaceSqlConnection_b.Close();
            return result;
        }
        private DaDeskPort FindPortByName(string p)
        {
            DaDeskPort result = null;
            m_dataBaseConectivity.InterfaceSqlConnection_b.Open();
            SqlCommand cmd = new SqlCommand(DA_Desk_SQL.FindPortByName(), m_dataBaseConectivity.InterfaceSqlConnection);
            cmd.Parameters.Add("@PortCode", SqlDbType.NVarChar).Value = p;
            SqlDataReader rd = cmd.ExecuteReader();
            while (rd.Read()) result = new DaDeskPort(rd);
            rd.Close();
            if (result == null)
            {
                result = new DaDeskPort();
                result.PortCode = p;
                result.AgrPortCode = p;
                cmd.CommandText = DA_Desk_SQL.InsertPort();
                cmd.Parameters.Clear();
                cmd.Parameters.Add("@PortCode", SqlDbType.NVarChar).Value = result.PortCode;
                cmd.Parameters.Add("@AgrPortCode", SqlDbType.NVarChar).Value = result.PortCode;
                cmd.ExecuteNonQuery();
            }
            m_dataBaseConectivity.InterfaceSqlConnection_b.Close();
            return result;
        }
        public void UpdateVessel(DaDeskVessel result)
        {
            m_dataBaseConectivity.InterfaceSqlConnection.Open();
            SqlCommand cmd = new SqlCommand(DA_Desk_SQL.UpdateVessel(), m_dataBaseConectivity.InterfaceSqlConnection);
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@AgrVessel_Code", SqlDbType.NVarChar).Value = result.AgrVessel_Code;
            cmd.Parameters.Add("@AgrVessel_Name", SqlDbType.NVarChar).Value = result.AgrVessel_Name;
            cmd.Parameters.Add("@AgrVessel_CostCentre", SqlDbType.NVarChar).Value = result.AgrVessel_CostCentre;
            cmd.Parameters.Add("@DaDeskVessel_id", SqlDbType.Int).Value = result.DaDeskVessel_id;
            cmd.ExecuteNonQuery();
            m_dataBaseConectivity.InterfaceSqlConnection.Close();
        }
        public void UpdateAgent(DaDeskAgent agent)
        {
            m_dataBaseConectivity.InterfaceSqlConnection.Open();
            SqlCommand cmd = new SqlCommand(DA_Desk_SQL.UpdateAgent(), m_dataBaseConectivity.InterfaceSqlConnection);
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@AgrAgent_Code", SqlDbType.NVarChar).Value = agent.AgrAgent_Code;
            cmd.Parameters.Add("@AgrAgent_Name", SqlDbType.NVarChar).Value = agent.AgrAgent_Name;
            cmd.Parameters.Add("@AgrClient", SqlDbType.NVarChar).Value = agent.AgrClient;
            cmd.Parameters.Add("@DaDeskAgent_id", SqlDbType.Int).Value = agent.DaDeskAgent_id;
            cmd.ExecuteNonQuery();
            m_dataBaseConectivity.InterfaceSqlConnection.Close();
        }
        public void UpdateCompany(DaDeskCompany company)
        {
            m_dataBaseConectivity.InterfaceSqlConnection.Open();
            SqlCommand cmd = new SqlCommand(DA_Desk_SQL.UpdateCompany(), m_dataBaseConectivity.InterfaceSqlConnection);
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@AgrCompany_Code", SqlDbType.NVarChar).Value = company.AgrCompany_Code;
            cmd.Parameters.Add("@AgrCompany_Name", SqlDbType.NVarChar).Value = company.AgrCompany_Name;
            cmd.Parameters.Add("@AgrClient", SqlDbType.NVarChar).Value = company.AgrClient;
            cmd.Parameters.Add("@DaDeskCompany_id", SqlDbType.Int).Value = company.DaDeskCompany_id;
            cmd.ExecuteNonQuery();
            m_dataBaseConectivity.InterfaceSqlConnection.Close();
        }
        public void UpdateAccount(DaDeskAccount company)
        {
            m_dataBaseConectivity.InterfaceSqlConnection.Open();
            SqlCommand cmd = new SqlCommand(DA_Desk_SQL.UpdateAccount(), m_dataBaseConectivity.InterfaceSqlConnection);
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@AccountCode", SqlDbType.NVarChar).Value = company.AccountCode;
            cmd.Parameters.Add("@AgrAccountCode", SqlDbType.NVarChar).Value = company.AgrAccountCode;
            cmd.Parameters.Add("@DaDeskAccount_id", SqlDbType.Int).Value = company.DaDeskAccount_id;
            cmd.ExecuteNonQuery();
            m_dataBaseConectivity.InterfaceSqlConnection.Close();
        }
        public void UpdatePort(DaDeskPort port)
        {
            m_dataBaseConectivity.InterfaceSqlConnection.Open();
            SqlCommand cmd = new SqlCommand(DA_Desk_SQL.UpdatePort(), m_dataBaseConectivity.InterfaceSqlConnection);
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@PortCode", SqlDbType.NVarChar).Value = port.PortCode;
            cmd.Parameters.Add("@AgrPortCode", SqlDbType.NVarChar).Value = port.AgrPortCode;
            cmd.Parameters.Add("@DaDeskPort_id", SqlDbType.Int).Value = port.DaDeskPort_id;
            cmd.ExecuteNonQuery();
            m_dataBaseConectivity.InterfaceSqlConnection.Close();
        }
        public void InsertBank(DaDeskBank bank)
        {
            m_dataBaseConectivity.InterfaceSqlConnection.Open();
            SqlCommand cmd = new SqlCommand(DA_Desk_SQL.InsertBank(), m_dataBaseConectivity.InterfaceSqlConnection);
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@da_type", SqlDbType.NVarChar).Value = bank.da_type;
            cmd.Parameters.Add("@biz_unit", SqlDbType.NVarChar).Value = bank.biz_unit;
            cmd.Parameters.Add("@account_code", SqlDbType.NVarChar).Value = bank.account_code;
            cmd.Parameters.Add("@bank_code", SqlDbType.NVarChar).Value = bank.bank_code;
            cmd.ExecuteNonQuery();
            cmd.CommandText = DA_Desk_SQL.GetMaxBankID();
            cmd.Parameters.Clear();
            bank.dadeskbank_id = Convert.ToInt32(cmd.ExecuteScalar());
            m_dataBaseConectivity.InterfaceSqlConnection.Close();
        }
        public void UpdateBank(DaDeskBank bank)
        {
            m_dataBaseConectivity.InterfaceSqlConnection.Open();
            SqlCommand cmd = new SqlCommand(DA_Desk_SQL.UpdateBank(), m_dataBaseConectivity.InterfaceSqlConnection);
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@da_type", SqlDbType.NVarChar).Value = bank.da_type;
            cmd.Parameters.Add("@biz_unit", SqlDbType.NVarChar).Value = bank.biz_unit;
            cmd.Parameters.Add("@account_code", SqlDbType.NVarChar).Value = bank.account_code;
            cmd.Parameters.Add("@bank_code", SqlDbType.NVarChar).Value = bank.bank_code;
            cmd.Parameters.Add("@dadeskbank_id", SqlDbType.Int).Value = bank.dadeskbank_id;
            cmd.ExecuteNonQuery();
            m_dataBaseConectivity.InterfaceSqlConnection.Close();
        }
        public void DeleteBank(DaDeskBank bank)
        {
            m_dataBaseConectivity.InterfaceSqlConnection.Open();
            SqlCommand cmd = new SqlCommand(DA_Desk_SQL.DeleteBank(), m_dataBaseConectivity.InterfaceSqlConnection);
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@dadeskbank_id", SqlDbType.Int).Value = bank.dadeskbank_id;
            cmd.ExecuteNonQuery();
            m_dataBaseConectivity.InterfaceSqlConnection.Close();
        }
        public void ImportDataFromXml()
        {
            //Console.WriteLine(DateTime.Now.ToString("o"));
            string dADeskFileFolder = ConfigurationManager.AppSettings["DADeskFileFolder"];
            string dADeskBackUpFileFolder = ConfigurationManager.AppSettings["DADeskBackUpFileFolder"];
            string[] files = System.IO.Directory.GetFiles(dADeskFileFolder);
            foreach (string file in files)
            {
                string ext = Path.GetExtension(file);
                if (ext.ToLower() != ".xml") continue;
                IList result = GetFromXML(file);
                if (result.Count > 0)
                {
                    foreach (DADesk_PDA pd in result)
                    {
                        InsertPDA(pd);
                    }
                    string destinationFile = dADeskBackUpFileFolder + Path.GetFileNameWithoutExtension(file) 
                        + "_"
                        + DateTime.Now.ToString("ddMMyyyyHHmmss")
                        +"."
                        + Path.GetExtension(file); //.Substring(file.LastIndexOf("\\") + 1);
                    File.Move(file, AssertFileExist(dADeskBackUpFileFolder, destinationFile));
                }
            }
        }
        public static string AssertFileExist(string destinationFolder, string file)
        {
            if (File.Exists(file))
            {
                string fileName = file.Substring(file.LastIndexOf("\\") + 1);
                string[] files = fileName.Split('.');
                string file1 = files[0];
                string file2 = files[1];
                file = destinationFolder + file1 + "_Copy." + file2;
                file = AssertFileExist(destinationFolder, file);
                return file;
            }
            return file;
        }
    }
}
